<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<!--
  Copyright © 2022 Petr Panteleyev <petr@panteleyev.org>
  SPDX-License-Identifier: BSD-2-Clause
  -->
<!--suppress SqlNoDataSourceInspection -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.1.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd"
                   logicalFilePath="v22.8.0/changelog.xml">

    <changeSet id="v22.8.0-icon" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE icon
            (
                uuid     UUID           NOT NULL PRIMARY KEY,
                name     VARCHAR(255)   NOT NULl,
                bytes    BYTEA,
                created  BIGINT         NOT NULL,
                modified BIGINT         NOT NULL
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE icon
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-category" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE category
            (
                uuid      UUID          NOT NULL PRIMARY KEY,
                name      VARCHAR(255)  NOT NULL,
                comment   VARCHAR(255)  NOT NULL,
                type      VARCHAR(255)  NOT NULL,
                icon_uuid UUID          DEFAULT NULL,
                created   BIGINT        NOT NULL,
                modified  BIGINT        NOT NULL,
                CONSTRAINT category_icon_fk FOREIGN KEY (icon_uuid)
                    REFERENCES icon (uuid) ON DELETE SET NULL ON UPDATE RESTRICT
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE category
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-contact" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE contact
            (
                uuid      UUID          NOT NULL PRIMARY KEY,
                name      VARCHAR(255)  NOT NULL,
                type      VARCHAR(255)  NOT NULL,
                phone     VARCHAR(255)  NOT NULL,
                mobile    VARCHAR(255)  NOT NULL,
                email     VARCHAR(255)  NOT NULL,
                web       VARCHAR(255)  NOT NULL,
                comment   VARCHAR(255)  NOT NULL,
                street    VARCHAR(255)  NOT NULL,
                city      VARCHAR(255)  NOT NULL,
                country   VARCHAR(255)  NOT NULL,
                zip       VARCHAR(255)  NOT NULL,
                icon_uuid UUID          DEFAULT NULL,
                created   BIGINT        NOT NULL,
                modified  BIGINT        NOT NULL,
                CONSTRAINT contact_icon_fk FOREIGN KEY (icon_uuid)
                    REFERENCES icon (uuid) ON DELETE SET NULL ON UPDATE RESTRICT
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE contact
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-currency" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE currency
            (
                uuid               UUID             NOT NULL PRIMARY KEY,
                symbol             VARCHAR(255)     NOT NULL,
                description        VARCHAR(255)     NOT NULL,
                format_symbol      VARCHAR(255)     NOT NULL,
                format_symbol_pos  INT              NOT NULL,
                show_format_symbol BOOLEAN          NOT NULL,
                def                BOOLEAN          NOT NULL,
                rate               DECIMAL(15, 6)   NOT NULL,
                rate_direction     INT              NOT NULL,
                use_th_separator   BOOLEAN          NOT NULL,
                created            BIGINT           NOT NULL,
                modified           BIGINT           NOT NULL
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE currency
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-account" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE account
            (
                uuid          UUID              NOT NULL PRIMARY KEY,
                name          varchar(255)      NOT NULL,
                comment       varchar(255)      NOT NULL,
                number        varchar(255)      NOT NULL,
                opening       decimal(15, 6)    NOT NULL,
                account_limit decimal(15, 6)    NOT NULL,
                rate          decimal(15, 6)    NOT NULL,
                type          varchar(255)      NOT NULL,
                category_uuid UUID              NOT NULL,
                currency_uuid UUID              DEFAULT NULL,
                enabled       BOOLEAN           NOT NULL,
                interest      decimal(15, 6)    NOT NULL,
                closing_date  bigint            DEFAULT NULL,
                icon_uuid     UUID              DEFAULT NULL,
                card_type     varchar(255)      NOT NULL,
                card_number   varchar(255)      NOT NULL,
                total         decimal(15, 6)    NOT NULL,
                total_waiting decimal(15, 6)    NOT NULL,
                created       bigint            NOT NULL,
                modified      bigint            NOT NULL,
                CONSTRAINT account_category_fk FOREIGN KEY (category_uuid)
                    REFERENCES category (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT account_currency_fk FOREIGN KEY (currency_uuid)
                    REFERENCES currency (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT account_icon_fk FOREIGN KEY (icon_uuid)
                    REFERENCES icon (uuid) ON DELETE SET NULL ON UPDATE RESTRICT
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE account
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-transaction" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE transaction
            (
                uuid                       UUID             NOT NULL PRIMARY KEY,
                amount                     DECIMAL(15, 6)   NOT NULL,
                date_day                   INT              NOT NULL,
                date_month                 INT              NOT NULL,
                date_year                  INT              NOT NULL,
                type                       VARCHAR(255)     NOT NULL,
                comment                    VARCHAR(255)     NOT NULL,
                checked                    BOOLEAN          NOT NULL,
                acc_debited_uuid           UUID             NOT NULL,
                acc_credited_uuid          UUID             NOT NULL,
                acc_debited_type           VARCHAR(255)     NOT NULL,
                acc_credited_type          VARCHAR(255)     NOT NULL,
                acc_debited_category_uuid  UUID             NOT NULL,
                acc_credited_category_uuid UUID             NOT NULL,
                contact_uuid               UUID             DEFAULT NULL,
                rate                       DECIMAL(15, 6)   NOT NULL,
                rate_direction             INT              NOT NULL,
                invoice_number             VARCHAR(255)     NOT NULL,
                parent_uuid                UUID             DEFAULT NULL,
                detailed                   BOOLEAN          NOT NULL,
                statement_date             BIGINT           NOT NULL,
                created                    BIGINT           NOT NULL,
                modified                   BIGINT           NOT NULL,
                CONSTRAINT transaction_debited_account_fk FOREIGN KEY (acc_debited_uuid)
                    REFERENCES account (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT transaction_credited_account_fk FOREIGN KEY (acc_credited_uuid)
                    REFERENCES account (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT transaction_debited_category_fk FOREIGN KEY (acc_debited_category_uuid)
                    REFERENCES category (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT transaction_credited_category_fk FOREIGN KEY (acc_credited_category_uuid)
                    REFERENCES category (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT transaction_contact_fk FOREIGN KEY (contact_uuid)
                    REFERENCES contact (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT,
                CONSTRAINT transaction_transaction_fk FOREIGN KEY (parent_uuid)
                    REFERENCES transaction (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT
            )
        </sql>
        <rollback>
            <sql>
                DROP TABLE transaction
            </sql>
        </rollback>
    </changeSet>

    <changeSet id="v22.8.0-document" author="petr-panteleyev@yandex.ru">
        <sql>
            CREATE TABLE document
            (
                uuid          UUID          NOT NULL PRIMARY KEY,
                owner_uuid    UUID,
                contact_uuid  UUID          NOT NULL,
                document_type VARCHAR(255)  NOT NULL,
                file_name     VARCHAR(255)  NOT NULL,
                file_date     BIGINT        NOT NULL,
                file_size     INT           NOT NULL,
                mime_type     VARCHAR(255)  NOT NULL,
                description   VARCHAR(255)  NOT NULL,
                content       BYTEA,
                created       BIGINT        NOT NULL,
                modified      BIGINT        NOT NULL,
                CONSTRAINT document_contact_fk FOREIGN KEY (contact_uuid)
                    REFERENCES contact (uuid) ON DELETE RESTRICT ON UPDATE RESTRICT
            );
        </sql>
        <rollback>
            <sql>
                DROP TABLE document
            </sql>
        </rollback>
    </changeSet>

</databaseChangeLog>
